import json
import os
import random
import time

from sqlalchemy import (
    Boolean,
    ForeignKey,
    Integer,
    String,
    create_engine,
    distinct,
    func,
)
from sqlalchemy.dialects.postgresql import ARRAY, JSONB
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    backref,
    mapped_column,
    relationship,
    sessionmaker,
)

from utils import get_random_address, get_random_coffee

engine = create_engine(os.getenv("DATABASE_URL"), echo=True)
Session = sessionmaker(bind=engine)
session = Session()


class Base(DeclarativeBase):
    def to_json(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Coffee(Base):
    __tablename__ = "coffee"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200), nullable=False)
    origin: Mapped[str] = mapped_column(String(200), nullable=True)
    intensifier: Mapped[str] = mapped_column(String(100), nullable=True)
    notes: Mapped[list[str]] = mapped_column(ARRAY(String), nullable=True)

    def __repr__(self) -> str:
        return (
            f"Coffee(id={self.id}, title={self.title}, origin={self.origin}, "
            f"intensifier={self.intensifier}, notes={self.notes})"
        )


class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50), nullable=False)
    surname: Mapped[str] = mapped_column(String(50), nullable=True)
    patronomic: Mapped[str] = mapped_column(String(50), nullable=True)
    has_sale: Mapped[bool] = mapped_column(Boolean, server_default="False")
    address: Mapped[dict] = mapped_column(JSONB, nullable=True)
    coffee_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("coffee.id"), nullable=True
    )
    coffee = relationship(
        "Coffee", backref=backref("users", cascade="all, delete-orphan")
    )

    def __repr__(self) -> str:
        return f"User(id={self.id}, name={self.name})"

    def to_json(self):
        user_json = super().to_json()
        if self.coffee_id:
            del user_json["coffee_id"]
            user_json["coffee"] = self.coffee.to_json()
        return user_json


def initialize_db():
    Base.metadata.create_all(engine)

    # Создаём рандомные данные только если таблица ещё пустая:
    count = session.query(func.count(User.id)).scalar()
    if not count:
        _create_initial_data()


def _create_initial_data():
    names = ["Alexander", "Michael", "Vladimir", "Oleg", "Igor", "Sergey", "Marat"]

    print("Generating initial data... It will take several seconds.")

    coffees = _create_random_coffee(size=10)

    time.sleep(1.5)

    users = []
    addresses = json.loads(get_random_address(size=10))
    for address in addresses:
        name = random.choice(names)
        # NB: передаем адрес как `dict`:
        user = User(name=name, address=address)
        coffee = random.choice(coffees)
        coffee.users.append(user)
        users.append(user)

    records = []
    records.extend(users)
    records.extend(coffees)
    session.add_all(records)
    session.commit()

    print("Initial data created.")


def _create_random_coffee(size: int = 1) -> list[Coffee]:
    data = get_random_coffee(size=size)
    coffee_data = json.loads(data)

    result: list[Coffee] = []
    for coffee_dict in coffee_data:
        notes = coffee_dict["notes"].split(", ")
        result.append(
            Coffee(
                title=coffee_dict["blend_name"],
                origin=coffee_dict["origin"],
                intensifier=coffee_dict["intensifier"],
                notes=notes,
            )
        )
    return result


def _get_all_users(country: str = None):
    if country:
        return (
            session.query(User)
            .filter(User.address["country"].as_string() == country)
            .all()
        )
    return session.query(User).all()


def get_all_users_json(country: str = None) -> list[dict]:
    users = []
    for user in _get_all_users(country):
        users.append(user.to_json())
    return users


def _get_all_coffee(title: str = None):
    if title:
        return session.query(Coffee).where(Coffee.title.match(title)).all()
    return session.query(Coffee).all()


def get_all_coffee_json(title: str = None) -> list[dict]:
    coffees = []
    for coffee in _get_all_coffee(title):
        coffees.append(coffee.to_json())
    return coffees


def get_unique_notes() -> list[str]:
    # result will be an array of single-element tuples:
    result = session.query(distinct(func.unnest(Coffee.notes).label("value"))).all()
    notes = [str(elem[0]) for elem in result]
    return notes


def create_user(name: str, coffee_id: int | None, address: dict | None) -> dict:
    user = User(name=name, coffee_id=coffee_id, address=address)
    session.add(user)
    session.commit()
    return user.to_json()
